﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using KnitwearCompany.Models;

namespace KnitwearCompany.Managers
{
    public class Manager
    {
    
        private const string connectionString =
            "Data Source=YAHOO\\SQLEXPRESS;Initial Catalog=KnitwearCompany;Integrated Security=True;Pooling=False";
        protected SqlConnection Connection { get; set; }

        protected void Connect()
        {
            Connection = new SqlConnection(connectionString);
            Connection.Open();
        }

        protected void Disconnect()
        {
            Connection.Close();
        }

        public List<Category> GetCategories()
        {
            Connect();
            List<Category> list = new List<Category>();

            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Categories", Connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category category = new Category();
                    category.Id = Convert.ToInt32(reader["id"]);
                    category.Name = Convert.ToString(reader["name"]);
                    list.Add(category);
                }
                reader.Close();
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
            return list;
        }

        public bool AddCategory(Category category)
        {
            bool result = true;
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "INSERT INTO Categories (name) " +
                    "VALUES (@name)", Connection);
                cmd.Parameters.Add(new SqlParameter("@name", category.Name));
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                Disconnect();
            }
            return result;
        }

        public Category GetCategory(int id)
        {
            Connect();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Categories WHERE id=@id", Connection);
                command.Parameters.Add(new SqlParameter("id", Convert.ToString(id)));
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                Category category = new Category();
                category.Id = Convert.ToInt32(reader["id"]);
                category.Name = Convert.ToString(reader["name"]);
                reader.Close();
                return category;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        public Category GetCategory(string name)
        {
            Connect();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Categories WHERE name=@name", Connection);
                command.Parameters.Add(new SqlParameter("name", name));
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                Category category = new Category();
                category.Id = Convert.ToInt32(reader["id"]);
                category.Name = Convert.ToString(reader["name"]);
                reader.Close();
                return category;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        public bool UpdateCategory(Category category)
        {
            bool result = true;
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "UPDATE Categories SET name=@name " +
                    "WHERE id=@id", Connection);
                cmd.Parameters.Add(new SqlParameter("@id", category.Id));
                cmd.Parameters.Add(new SqlParameter("@name", category.Name));
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                Disconnect();
            }
            return result;
        }

        public void DeleteCategory(int id)
        {
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand("DELETE FROM Categories WHERE id=@id", Connection);
                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.ExecuteNonQuery();
            }
            finally
            {
                Disconnect();
            }
        }

        //==========================
        // products
        //==========================
        public List<Product> GetProducts()
        {
            Connect();
            var list = new List<Product>();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Products", Connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Product product = new Product();
                    product.Id = Convert.ToInt32(reader["id"]);
                    product.Name = Convert.ToString(reader["name"]);
                    product.Description = Convert.ToString(reader["description"]);
                    product.Price = (decimal)Convert.ToDouble(reader["price"]);
                    int categoryId = Convert.ToInt32(reader["category"]);
                    product.Category = GetCategory(categoryId);
                    list.Add(product);
                }
                reader.Close();
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
            return list;
        }

        public List<Product> GetProducts(int categoryId)
        {
            Connect();
            var list = new List<Product>();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Products where category=@category", Connection);
                command.Parameters.Add(new SqlParameter("@category", categoryId));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Product product = new Product();
                    product.Id = Convert.ToInt32(reader["id"]);
                    product.Name = Convert.ToString(reader["name"]);
                    product.Description = Convert.ToString(reader["description"]);
                    product.Price = (decimal)Convert.ToDouble(reader["price"]);
                    categoryId = Convert.ToInt32(reader["category"]);
                    product.Category = GetCategory(categoryId);
                    list.Add(product);
                }
                reader.Close();
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
            return list;
        }

        public bool AddProduct(Product product)
        {
            bool result = true;
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "INSERT INTO Products (name,description,price,category) " +
                    "VALUES (@name,@description,@price,@category)", Connection);
                cmd.Parameters.Add(new SqlParameter("@name", product.Name));
                cmd.Parameters.Add(new SqlParameter("@description", product.Description));
                cmd.Parameters.Add(new SqlParameter("@price", product.Price));
                cmd.Parameters.Add(new SqlParameter("@category", product.Category.Id));
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                Disconnect();
            }
            return result;
        }

        public Product GetProduct(int id)
        {
            Connect();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Products WHERE id=@id", Connection);
                command.Parameters.Add(new SqlParameter("id", Convert.ToString(id)));
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                Product product = new Product();
                product.Id = Convert.ToInt32(reader["id"]);
                product.Name = Convert.ToString(reader["name"]);
                product.Description = Convert.ToString(reader["description"]);
                product.Price = (decimal) Convert.ToDouble(reader["price"]);
                int categoryId = Convert.ToInt32(reader["category"]);
                reader.Close();
                product.Category = GetCategory(categoryId);
                return product;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Disconnect();
            }

        }

        public bool UpdateProduct(Product product)
        {
            bool result = true;
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "UPDATE Products SET name=@name,description=@description,price=@price,category=@category " +
                    "WHERE id=@id", Connection);
                cmd.Parameters.Add(new SqlParameter("@id", product.Id));
                cmd.Parameters.Add(new SqlParameter("@name", product.Name));
                cmd.Parameters.Add(new SqlParameter("@description", product.Description));
                cmd.Parameters.Add(new SqlParameter("@price", product.Price));
                cmd.Parameters.Add(new SqlParameter("@category", product.Category.Id));
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                Disconnect();
            }
            return result;
        }

        public void DeleteProduct(int id)
        {
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand("DELETE FROM Products WHERE id=@id", Connection);
                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.ExecuteNonQuery();
            }
            finally
            {
                Disconnect();
            }
        }

        //==========================
        // users
        //==========================

        public List<User> GetUsers()
        {
            Connect();
            var list = new List<User>();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Users", Connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    User user = new User();
                    user.Id = Convert.ToInt32(reader["id"]);
                    user.Login = Convert.ToString(reader["login"]);
                    user.Password = Convert.ToString(reader["password"]);
                    user.IsAdmin = Convert.ToBoolean(reader["admin"]);
                    list.Add(user);
                }
                reader.Close();
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
            return list;
        }

        public bool AddUser(User user)
        {
            bool result = true;
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "INSERT INTO Users (login,password,admin) " +
                    "VALUES (@login,@password,@admin)", Connection);
                cmd.Parameters.Add(new SqlParameter("@login", user.Login));
                cmd.Parameters.Add(new SqlParameter("@password", user.Password));
                cmd.Parameters.Add(new SqlParameter("@admin", user.IsAdmin));
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                Disconnect();
            }
            return result;
        }

        public User GetUser(int id)
        {
            Connect();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE id=@id", Connection);
                command.Parameters.Add(new SqlParameter("id", id.ToString()));
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                User user = new User();
                user.Id = Convert.ToInt32(reader["id"]);
                user.Login = Convert.ToString(reader["login"]);
                user.Password = Convert.ToString(reader["password"]);
                user.IsAdmin = (bool)reader["admin"];
                reader.Close();
                return user;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Disconnect();
            }
        }
        
        public User GetUser(string login)
        {
            Connect();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE login=@login", Connection);
                command.Parameters.Add(new SqlParameter("login", login));
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                User user = new User();
                user.Id = Convert.ToInt32(reader["id"]);
                user.Login = Convert.ToString(reader["login"]);
                user.Password = Convert.ToString(reader["password"]);
                user.IsAdmin =(bool)reader["admin"];
                reader.Close();
                return user;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        public bool UpdateUser(User user)
        {
            bool result = true;
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "UPDATE Users SET login=@login,password=@password,admin=@admin " +
                    "WHERE id=@id", Connection);
                cmd.Parameters.Add(new SqlParameter("@id", user.Id));
                cmd.Parameters.Add(new SqlParameter("@login", user.Login));
                cmd.Parameters.Add(new SqlParameter("@password", user.Password));
                cmd.Parameters.Add(new SqlParameter("@admin", user.IsAdmin));
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                Disconnect();
            }
            return result;
        }

        public void DeleteUser(int id)
        {
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand("DELETE FROM Users WHERE id=@id", Connection);
                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.ExecuteNonQuery();
            }
            finally
            {
                Disconnect();
            }
        }

        //==========================
        // orders
        //==========================

        public List<Order> GetOrders()
        {
            Connect();
            var list = new List<Order>();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Orders", Connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Order order = new Order();
                    order.Id = Convert.ToInt32(reader["id"]);
                    order.User = GetUser(Convert.ToInt32(reader["customer"]));
                    order.Address = Convert.ToString(reader["address"]);
                    order.Status = GetStatus(Convert.ToInt32(reader["status"]));
                    order.Date = Convert.ToDateTime(reader["date"]);
                    order.Comment = Convert.ToString(reader["comment"]);
                    order.Purchases = GetPurchaseItems(order.Id);
                    list.Add(order);
                }
                reader.Close();
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
            return list;
        }

        public Order AddOrder(Order order)
        {
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "INSERT INTO Orders (customer,address,status,date,comment) " +
                    "VALUES (@customer,@address,@status,@date,@comment)", Connection);
                cmd.Parameters.Add(new SqlParameter("@customer", order.User.Id));
                cmd.Parameters.Add(new SqlParameter("@address", order.Address));
                cmd.Parameters.Add(new SqlParameter("@status", order.Status.Id));
                cmd.Parameters.Add(new SqlParameter("@date", order.Date));
                cmd.Parameters.Add(new SqlParameter("@comment", order.Comment));
                cmd.ExecuteNonQuery();

                SqlCommand command = new SqlCommand("SELECT * FROM Orders WHERE customer=@customer AND date=@date and status=@status", Connection);
                command.Parameters.Add(new SqlParameter("customer", order.User.Id));
                command.Parameters.Add(new SqlParameter("date", order.Date));
                command.Parameters.Add(new SqlParameter("status", order.Status.Id));
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                order.Id = Convert.ToInt32(reader["id"]);

                AddPurchaseItems(order.Id, order.Purchases);
                order.Purchases = GetPurchaseItems(order.Id);

                reader.Close();
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
            return order;
        }

        public Order GetOrder(int id)
        {
            Connect();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Orders WHERE id=@id", Connection);
                command.Parameters.Add(new SqlParameter("id", Convert.ToString(id)));
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                Order order = new Order();
                order.Id = Convert.ToInt32(reader["id"]);
                order.User = GetUser(Convert.ToInt32(reader["customer"]));
                order.Address = Convert.ToString(reader["address"]);
                order.Status = GetStatus(Convert.ToInt32(reader["status"]));
                order.Date = Convert.ToDateTime(reader["date"]);
                order.Comment = Convert.ToString(reader["comment"]);
                order.Purchases = GetPurchaseItems(order.Id);
                reader.Close();
                return order;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        public void DeleteOrder(int id)
        {
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand("DELETE FROM PurchaseItems WHERE order_id=@order_id", Connection);
                cmd.Parameters.Add(new SqlParameter("@order_id", id));
                cmd.ExecuteNonQuery();
                cmd = new SqlCommand("DELETE FROM Orders WHERE id=@id", Connection);
                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.ExecuteNonQuery();
            }
            finally
            {
                Disconnect();
            }
        }

        public bool UpdateOrderStatus(Order order)
        {
            bool result = true;
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "UPDATE Orders SET status=@status " +
                    "WHERE id=@id", Connection);
                cmd.Parameters.Add(new SqlParameter("@id", order.Id));
                cmd.Parameters.Add(new SqlParameter("@status", order.Status.Id));
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                Disconnect();
            }
            return result;
        }

        //==========================
        // status
        //==========================

        public List<Status> GetStatuses()
        {
            Connect();
            var list = new List<Status>();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Statuses", Connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Status status = new Status();
                    status.Id = Convert.ToInt32(reader["id"]);
                    status.Name = Convert.ToString(reader["name"]);
                    list.Add(status);
                }
                reader.Close();
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
            return list;
        }

        public bool AddStatus(Status status)
        {
            bool result = true;
            Connect();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    "INSERT INTO Statuses (name) " +
                    "VALUES (@name)", Connection);
                cmd.Parameters.Add(new SqlParameter("@name", status.Name));
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                Disconnect();
            }
            return result;
        }

        public Status GetStatus(int id)
        {
            Connect();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Statuses WHERE id=@id", Connection);
                command.Parameters.Add(new SqlParameter("id", Convert.ToString(id)));
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                Status status = new Status();
                status.Id = Convert.ToInt32(reader["id"]);
                status.Name = Convert.ToString(reader["name"]);
                reader.Close();
                return status;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        //==========================
        // purchase items
        //==========================
        public List<PurchaseItem> GetPurchaseItems(int orderId)
        {
            Connect();
            var list = new List<PurchaseItem>();
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM PurchaseItems WHERE order_id=@order_id", Connection);
                command.Parameters.Add(new SqlParameter("order_id", Convert.ToString(orderId)));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    PurchaseItem item = new PurchaseItem();
                    item.Id = Convert.ToInt32(reader["id"]);
                    item.Product = GetProduct(Convert.ToInt32(reader["product"]));
                    item.Amount = Convert.ToInt32(reader["amount"]);
                    list.Add(item);
                }
                reader.Close();
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
            return list;
        }

        public void AddPurchaseItems(int orderId, List<PurchaseItem> items)
        {
            Connect();
            try
            {
                foreach (var item in items)
                {
                    SqlCommand cmd = new SqlCommand(
                        "INSERT INTO PurchaseItems (product,amount,order_id) " +
                        "VALUES (@product,@amount,@order_id)", Connection);
                    cmd.Parameters.Add(new SqlParameter("@product", item.Product.Id));
                    cmd.Parameters.Add(new SqlParameter("@amount", item.Amount));
                    cmd.Parameters.Add(new SqlParameter("@order_id", orderId));
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception) { }
            finally
            {
                Disconnect();
            }
        }
    }

}